﻿using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Net.NetworkInformation;
using System.Reflection;
using System.Xml.Linq;
using TurtleEngine.Properties;

namespace TurtleEngine.Databases
{
	 /// <summary>
	 ///Sub class that implements the database connectivity code specifically for SQL Server
	 /// </summary>
	 public class SqlServer : BaseConnect
	 {
		  /// <summary>
		  /// Stores the name of the file that could be associated with the database
		  /// </summary>
		  private string _attachDbFile;

		  /// <summary>
		  /// The SQL Server connection object
		  /// </summary>
		  private SqlConnection _connection;

		  /// <summary>
		  /// The name of the database instance
		  /// </summary>
		  private string _databaseName;

		  /// <summary>
		  /// Flag indicating if you log in using Windows integrated authentication or SQL authentixcation
		  /// </summary>
		  private bool _integrated;

		  /// <summary>
		  /// User's password
		  /// </summary>
		  private string _password;

		  /// <summary>
		  /// Name of the server that SQL Server is installed on
		  /// </summary>
		  private string _serverName;

		  /// <summary>
		  /// User's username
		  /// </summary>
		  private string _username;

		  /// <summary>
		  /// Initializes a new instance of the <see cref="SqlServer" /> class.
		  /// </summary>
		  public SqlServer()
		  {
				_connection = new SqlConnection();
				GetQueries(XDocument.Parse(Resources.SQLServerQueries));
		  }

		  /// <summary>
		  /// Adds the column details.
		  /// </summary>
		  /// <param name="columnInfo">Datarow that stores the information for the relevant column.</param>
		  /// <exception cref="System.NotImplementedException"></exception>
		  public override void AddColumnDetails(DataRow columnInfo)
		  {
				throw new NotImplementedException();
		  }

		  /// <summary>
		  /// Adds the function details.
		  /// </summary>
		  /// <param name="functionInfo">Datarow that stores the information for the relevant function.</param>
		  /// <exception cref="System.NotImplementedException"></exception>
		  public override void AddFunctionDetails(DataRow functionInfo)
		  {
				throw new NotImplementedException();
		  }

		  /// <summary>
		  /// Adds the procedure details.
		  /// </summary>
		  /// <param name="procedureInfo">Datarow that stores the information for the relevant stored procedure.</param>
		  /// <exception cref="System.NotImplementedException"></exception>
		  public override void AddProcedureDetails(DataRow procedureInfo)
		  {
				throw new NotImplementedException();
		  }

		  /// <summary>
		  /// Adds the table details.
		  /// </summary>
		  /// <param name="tableInfo">Datarow that stores the information for the relevant table.</param>
		  /// <exception cref="System.NotImplementedException"></exception>
		  public override void AddTableDetails(DataRow tableInfo)
		  {
				throw new NotImplementedException();
		  }

		  /// <summary>
		  /// Adds the trigger details.
		  /// </summary>
		  /// <param name="triggerInfo">Datarow that stores the information for the relevant trigger.</param>
		  /// <exception cref="System.NotImplementedException"></exception>
		  public override void AddTriggerDetails(DataRow triggerInfo)
		  {
				throw new NotImplementedException();
		  }

		  /// <summary>
		  /// Adds the user-defined data type details.
		  /// </summary>
		  /// <param name="uddtInfo">Datarow that stores the information for the relevant user-defined data type.</param>
		  /// <exception cref="System.NotImplementedException"></exception>
		  public override void AddUddtDetails(DataRow uddtInfo)
		  {
				throw new NotImplementedException();
		  }

		  /// <summary>
		  /// Adds the view details.
		  /// </summary>
		  /// <param name="viewInfo">Datarow that stores the information for the relevant view.</param>
		  /// <exception cref="System.NotImplementedException"></exception>
		  public override void AddViewDetails(DataRow viewInfo)
		  {
				throw new NotImplementedException();
		  }

		  /// <summary>
		  /// Creates the connection to the database.
		  /// </summary>
		  /// <param name="connectionString">The connection string.</param>
		  /// <returns></returns>
		  public override bool Connect(string connectionString)
		  {
				try
				{
					 _connection = new SqlConnection(connectionString);
					 base.Connect(connectionString);
					 return true;
				}
				catch (Exception)
				{
					 return false;
				}
		  }

		  /// <summary>
		  /// Builds the connection string from the data gathered.
		  /// </summary>
		  /// <returns></returns>
		  /// <exception cref="System.NotImplementedException"></exception>
		  public override string ConnectionStringBuilder()
		  {
				// If we're changing the connection string, we clearly don't need to keep the current connectiopn open (if any)
				if (_connection.State == ConnectionState.Open)
				{
					 _connection.Close();
				}

				// If we have no server details, we send it back empty
				if (String.IsNullOrEmpty(_serverName) || String.IsNullOrEmpty(_databaseName))
				{
					 return String.Empty;
				}

				// If we are using integrated security and no database file
				if ((_integrated) && (String.IsNullOrEmpty(_attachDbFile)))
				{
					 return String.Format("Server={0};Database={1};Trusted_Connection=True;", _serverName, _databaseName);
				}

				// If we're not using integrated security or a database file
				if ((!_integrated) && (String.IsNullOrEmpty(_attachDbFile)))
				{
					 return String.Format("Server={0};Database={1};User Id={2};Password={3};", _serverName, _databaseName, _username, _password);
				}

				// If we are using a database file and integrated security
				if ((!String.IsNullOrEmpty(_attachDbFile)) && (_integrated))
				{
					 return String.Format("Server={0};AttachDbFilename={2};Database={1};Trusted_Connection=True;", _serverName, _databaseName, _attachDbFile);
				}

				// If we are using a database file and not using integrated security
				if ((!String.IsNullOrEmpty(_attachDbFile)) && (!_integrated))
				{
					 return String.Format("Server={0};AttachDbFilename={4};Database={1};User Id={2};Password={3};", _serverName, _databaseName, _username, _password, _attachDbFile);
				}

				// We should have covered all angles but if not, we send back an empty string
				return String.Empty;
		  }

		  /// <summary>
		  /// Closes the database connection.
		  /// </summary>
		  public override void Disconnect()
		  {
				_connection.Close();
				base.Disconnect();
		  }

		  /// <summary>
		  /// Finds if there are database instances on the given server.
		  /// </summary>
		  /// <param name="server">The server.</param>
		  /// <returns></returns>
		  /// <exception cref="System.NotImplementedException"></exception>
		  public override bool FindDatabaseInstances(string server)
		  {
				DataTable tblDatabases = _connection.GetSchema("Databases");
				if (tblDatabases.Rows.Count > 0)
					 return true;

				return false;
		  }

		  /// <summary>
		  /// Finds the database servers.
		  /// </summary>
		  /// <returns></returns>
		  public override DataTable FindDatabaseServers()
		  {
				SqlDataSourceEnumerator sqldatasourceenumerator1 = SqlDataSourceEnumerator.Instance;
				return sqldatasourceenumerator1.GetDataSources();
		  }

		  /// <summary>
		  /// Gets the column details.
		  /// </summary>
		  /// <param name="table">The table.</param>
		  /// <param name="column">The column.</param>
		  /// <returns></returns>
		  /// <exception cref="System.NotImplementedException"></exception>
		  public override DataRow GetColumnDetails(string table, string column)
		  {
				return GetInfoFromDatabase(Queries.Find(x => x.Name == "GetColumnDetails"), table, column).Rows[0];
		  }

		  /// <summary>
		  /// Gets the database instances from the given server.
		  /// </summary>
		  /// <param name="server">The server.</param>
		  /// <returns></returns>
		  /// <exception cref="System.NotImplementedException"></exception>
		  public override DataTable GetDatabaseInstances(string server)
		  {
				return _connection.GetSchema("Databases");
		  }

		  /// <summary>
		  /// Gets all functions in database
		  /// </summary>
		  /// <returns>Table with a list of all the functions</returns>
		  public override DataTable GetFunctions()
		  {
				return _connection.GetSchema("Functions");
		  }

		  /// <summary>
		  /// Gets the index details.
		  /// </summary>
		  /// <param name="index">The procedure.</param>
		  /// <returns></returns>
		  public override DataRow GetIndexDetails(string index)
		  {
				return GetInfoFromDatabase(Queries.Find(x => x.Name == "GetIndexDetails"), index, null).Rows[0];
		  }

		  /// <summary>
		  /// Gets all indexes in database
		  /// </summary>
		  /// <returns>Table with a list of all the Indexes</returns>
		  public override DataTable GetIndexes()
		  {
				return _connection.GetSchema("Indexes");
		  }

		  /// <summary>
		  /// The general procedure to access the database and get the relevant data
		  /// </summary>
		  /// <param name="sourceQuery">Query object used to access the correct data</param>
		  /// <param name="val1">First parameter string</param>
		  /// <param name="val2">Second parameter string</param>
		  /// <returns></returns>
		  public override DataTable GetInfoFromDatabase(DbQuery sourceQuery, string val1, string val2)
		  {
				string query = String.Empty;
				switch (sourceQuery.ConditionType)
				{
					 case "AND":
						  query = sourceQuery.QueryString + " AND " + sourceQuery.Unique1 + " = '" + val1 + "'";
						  if (!String.IsNullOrEmpty(val2))
								query += " AND " + sourceQuery.Unique2 + " = '" + val2 + "'";
						  break;

					 case "WHERE":
						  query = sourceQuery.QueryString + " WHERE " + sourceQuery.Unique1 + " = '" + val1 + "'";
						  if (!String.IsNullOrEmpty(val2))
								query += " AND " + sourceQuery.Unique2 + " = '" + val2 + "'";
						  break;

					 case "STOREDPROC":
						  query = String.Empty;
						  break;
				}

				DataTable dt = new DataTable();
				if (String.IsNullOrEmpty(query))
				{
					 SqlDataAdapter da = new SqlDataAdapter();
					 SqlCommand sqlCommand = new SqlCommand(sourceQuery.QueryString, _connection);

					 if (String.IsNullOrEmpty(val1))
					 {
						  sqlCommand.Parameters.AddWithValue("@" + sourceQuery.Unique1, val1);
					 }

					 if (String.IsNullOrEmpty(val2))
					 {
						  sqlCommand.Parameters.AddWithValue("@" + sourceQuery.Unique2, val2);
					 }

					 da.SelectCommand = sqlCommand;
					 da.SelectCommand.CommandType = CommandType.StoredProcedure;

					 da.Fill(dt);
				}
				else
				{
					 // Create a command to extract the required data and assign it the connection string
					 SqlCommand cmd = new SqlCommand(query, _connection);
					 cmd.CommandType = CommandType.Text;

					 // Create a DataAdapter to run the command and fill the DataTable
					 SqlDataAdapter da = new SqlDataAdapter();
					 da.SelectCommand = cmd;
					 da.Fill(dt);
				}

				return dt;
		  }

		  /// <summary>
		  /// Gets the procedure details.
		  /// </summary>
		  /// <param name="procedure">The procedure.</param>
		  /// <returns></returns>
		  /// <exception cref="System.NotImplementedException"></exception>
		  public override DataRow GetProcedureDetails(string procedure)
		  {
				return GetInfoFromDatabase(Queries.Find(x => x.Name == "GettSPDetails"), procedure, null).Rows[0];
		  }

		  /// <summary>
		  /// Gets all stored procedures in database
		  /// </summary>
		  /// <returns>Table with a list of all the stored procedures</returns>
		  public override DataTable GetProcedures()
		  {
				return _connection.GetSchema("Procedures");
		  }

		  /// <summary>
		  /// Gets the table column details.
		  /// </summary>
		  /// <param name="table">The table.</param>
		  /// <returns></returns>
		  /// <exception cref="System.NotImplementedException"></exception>
		  public override DataTable GetTableColumnDetails(string table)
		  {
				return GetInfoFromDatabase(Queries.Find(x => x.Name == "GetTableColumns"), table, null);
		  }

		  /// <summary>
		  /// Gets the table details.
		  /// </summary>
		  /// <param name="table">The table.</param>
		  /// <returns></returns>
		  /// <exception cref="System.NotImplementedException"></exception>
		  public override DataRow GetTableDetails(string table)
		  {
				return GetInfoFromDatabase(Queries.Find(x => x.Name == "GetTableDetails"), table, null).Rows[0];
		  }

		  /// <summary>
		  /// Gets all tables in database
		  /// </summary>
		  /// <returns>Table with a list of all the tables</returns>
		  public override DataTable GetTables()
		  {
				return _connection.GetSchema("Tables");
		  }

		  /// <summary>
		  /// Gets the trigger details.
		  /// </summary>
		  /// <param name="trigger">The trigger.</param>
		  /// <returns></returns>
		  /// <exception cref="System.NotImplementedException"></exception>
		  public override DataRow GetTriggerDetails(string trigger)
		  {
				return GetInfoFromDatabase(Queries.Find(x => x.Name == "GetTriggerDetails"), trigger, null).Rows[0];
		  }

		  /// <summary>
		  /// Gets all triggers in database
		  /// </summary>
		  /// <returns>Table with a list of all the triggers</returns>
		  public override DataTable GetTriggers()
		  {
				return _connection.GetSchema("Triggers");
		  }

		  /// <summary>
		  /// Gets the user-defined data type details.
		  /// </summary>
		  /// <param name="uddt">The user-defined data type.</param>
		  /// <returns></returns>
		  /// <exception cref="System.NotImplementedException"></exception>
		  public override DataRow GetUddtDetails(string uddt)
		  {
				return GetInfoFromDatabase(Queries.Find(x => x.Name == "GetUDDTDetails"), uddt, null).Rows[0];
		  }

		  /// <summary>
		  /// Gets all UDDTs in database
		  /// </summary>
		  /// <returns>Table with a list of all the UDDTs</returns>
		  public override DataTable GetUddts()
		  {
				return _connection.GetSchema("UserDefinedTypes");
		  }

		  /// <summary>
		  /// Gets the view details.
		  /// </summary>
		  /// <param name="view">The view.</param>
		  /// <returns></returns>
		  /// <exception cref="System.NotImplementedException"></exception>
		  public override DataRow GetViewDetails(string view)
		  {
				return GetInfoFromDatabase(Queries.Find(x => x.Name == "GetViewDetails"), view, null).Rows[0];
		  }

		  /// <summary>
		  /// Gets all views in database
		  /// </summary>
		  /// <returns>Table with a list of all the views</returns>
		  public override DataTable GetViews()
		  {
				return _connection.GetSchema("Views");
		  }

		  /// <summary>
		  /// Sets the variables used to build the connection string.  Any fields not required should be null.
		  /// </summary>
		  /// <param name="userName">User name for the connection (if required)</param>
		  /// <param name="password">Password for the connection (if required)</param>
		  /// <param name="serverName">Name of the server.</param>
		  /// <param name="instanceName">Name of the database instance.</param>
		  /// <param name="dbFile">Name of the database file (if required)</param>
		  /// <param name="integrated">Integrated security flag.</param>
		  public void SetConnectionStringVariables(string userName, string password, string serverName, string instanceName,
																 string dbFile, bool integrated)
		  {
				_username = userName;
				_password = password;
				_serverName = serverName;
				_databaseName = instanceName;
				_attachDbFile = dbFile;
				_integrated = integrated;
		  }
	 }
}